﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using YunCMS.Model;

namespace YunCMS.DAL
{
    public abstract class ModuleDAL<T> where T:YCModule ,new()
    {                     

        #region abstract members
        /// <summary>
        ///  Table Name
        /// </summary>
        protected abstract string TableName
        {
            get;
        }

        protected abstract string AddQueryStr
        {
            get;
        }

        protected abstract string UpdateQueryStr
        {
            get;
        }

//        private const string FIELDS = @"[ID],[CategoryID],[Title],[Keywords],[Summary],[Description],
//                                        [Author],[AddTime],[Hits],[IsPopular],[ImageUrl],[LinkUrl],
//                                        [DiggTop],[DiggStep],[Source]";

        protected virtual T PopulatePartialModelFromDr(IDataReader dr)
        {
            T Obj = new T();
            Obj.ID = Convert.ToInt32(dr["ID"]);
            Obj.CategoryID = Convert.ToInt32(dr["CategoryID"]);
            Obj.Title = dr["Title"].ToString();
            Obj.Keywords = dr["Keywords"].ToString();
            Obj.Description = dr["Description"].ToString();
            if ((dr["AddTime"]) == DBNull.Value) { Obj.AddTime = null; } else { Obj.AddTime = Convert.ToDateTime(dr["AddTime"]); }
            Obj.Hits = Convert.ToInt32(dr["Hits"]);
            Obj.IsPopular = (bool)dr["IsPopular"];
            if ((dr["DiggTop"]) == DBNull.Value) { Obj.DiggTop = null; } else { Obj.DiggTop = Convert.ToInt32(dr["DiggTop"]); }
            if ((dr["DiggStep"]) == DBNull.Value) { Obj.DiggStep = null; } else { Obj.DiggStep = Convert.ToInt32(dr["DiggStep"]); }
            Obj.Author = dr["Author"].ToString();
            Obj.Source = dr["Source"].ToString();

            return Obj;
        }

        /// <summary>
        /// This method must be overwrite.
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        public virtual T PopulateModelFromDr(IDataReader dr)
        {
            T Obj = new T();

            Obj.ID = Convert.ToInt32(dr["ID"]);
            Obj.CategoryID = Convert.ToInt32(dr["CategoryID"]);
            Obj.Title = dr["Title"].ToString();
            Obj.Keywords = dr["Keywords"].ToString();
            Obj.Description = dr["Description"].ToString();
            Obj.IsHomePage = (bool)dr["IsHomePage"];
            if ((dr["AddTime"]) == DBNull.Value) { Obj.AddTime = null; } else { Obj.AddTime = Convert.ToDateTime(dr["AddTime"]); }
            if ((dr["UpdateTime"]) == DBNull.Value) { Obj.UpdateTime = null; } else { Obj.UpdateTime = Convert.ToDateTime(dr["UpdateTime"]); }
            if ((dr["Access"]) == DBNull.Value) { Obj.Access = null; } else { Obj.Access = Convert.ToInt32(dr["Access"]); }
            Obj.Hits = Convert.ToInt32(dr["Hits"]);
            Obj.IsPopular = (bool)dr["IsPopular"];
            if ((dr["DiggTop"]) == DBNull.Value) { Obj.DiggTop = null; } else { Obj.DiggTop = Convert.ToInt32(dr["DiggTop"]); }
            if ((dr["DiggStep"]) == DBNull.Value) { Obj.DiggStep = null; } else { Obj.DiggStep = Convert.ToInt32(dr["DiggStep"]); }
            Obj.Active = (bool)dr["Active"];
            Obj.Lang = dr["Lang"].ToString();
            Obj.Content = dr["Content"].ToString();
            Obj.Author = dr["Author"].ToString();
            Obj.Source = dr["Source"].ToString();

            return Obj;
        }

        protected abstract List<SqlParameter> GetSelfParams(T model); 

        protected virtual SqlParameter[] PrepareSqlParams(T model, bool isUpdate)
        {
           List<SqlParameter> paramList =  GetParameterList(model, isUpdate);

           paramList.AddRange(GetSelfParams(model));
           return paramList.ToArray();
        }

        #endregion

        /// <summary>
        /// Insert a model to Database
        /// </summary>
        /// <param name="model">YCModuleArticalnew model</param>
        /// <returns>The new item Id</returns>
        public virtual int Add(T model)
        {
            return SqlHelper.ExecuteNonQuery(CommandType.Text, this.AddQueryStr, PrepareSqlParams(model, false));
        }
        /// <summary>
        /// Insert a model to Database in transaction.
        /// </summary>
        /// <param name="trans">SqlTransaction</param>
        /// <param name="model">YCModuleArticalnew model</param>
        /// <returns>The new item Id</returns>
        public virtual int Add(SqlTransaction trans, T model)
        {
            return SqlHelper.ExecuteNonQuery(trans, CommandType.Text, this.AddQueryStr, PrepareSqlParams(model, false));
        }

        /// <summary>
        /// update a model to db
        /// </summary>
        /// <param name="model">YCModuleArtical</param>
        /// <returns>rows affected</returns>
        public virtual int Update(T model)
        {
            return SqlHelper.ExecuteNonQuery(CommandType.Text, this.UpdateQueryStr, PrepareSqlParams(model, true));
        }

        /// <summary>
        /// update a model to db in transaction
        /// </summary>
        /// <param name="trans">SqlTransaction</param>
        /// <param name="model">YCModuleArtical</param>
        /// <returns>rows affected</returns>
        public virtual int Update(SqlTransaction trans, T model)
        {
            return SqlHelper.ExecuteNonQuery(trans, CommandType.Text, this.UpdateQueryStr, PrepareSqlParams(model, true));
        }   

        /// <summary>
        /// delete a model from db
        /// </summary>
        /// <param name="id">ID</param>
        /// <returns>rows affected</returns>
        public virtual int Delete(int id)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append(string.Format("delete {0} ", TableName));
            sqlStr.Append(" where ID=@ID");
            SqlParameter[] parameters ={
							new SqlParameter("@ID",SqlDbType.Int),
			
			};
            parameters[0].Value = id;
            return SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), parameters);
        }

        /// <summary>
        /// delete a model from db in transaction
        /// </summary>        
        /// <param name="trans">SqlTransaction</param>
        /// <param name="id">ID</param>
        /// <returns>rows affected</returns>
        public virtual int Delete(SqlTransaction trans, int id)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append(string.Format("delete {0} ", TableName));
            sqlStr.Append(" where ID=@ID");
            SqlParameter[] parameters ={
							new SqlParameter("@ID",SqlDbType.Int)			                
			};
            parameters[0].Value = id;
            return SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlStr.ToString(), parameters);
        }

        /// <summary>
        /// if exist a record according by ID
        /// </summary>
        /// <param name="id">id</param>
        /// <returns>true/false</returns>
        public bool IsExist(int id)
        {
            SqlParameter[] parameters ={
                                      new SqlParameter("@ID",SqlDbType.Int)
                                  };
            parameters[0].Value = id;
            string sqlStr = string.Format("select Count(1) from {0} where ID=@ID", TableName);
            int a = Convert.ToInt32(SqlHelper.ExecuteScalar(CommandType.Text, sqlStr, parameters));
            if (a > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        protected virtual string parseQueryStr(string check, string top)
        {
            string checkQuery = "";
            string topQuery = "";
            if (check.Equals("1"))
            {
                checkQuery = " and active=0";
            }
            else if (check.Equals("2"))
            {
                checkQuery = " and active=1";
            }

            if (top.Equals("1"))
            {
                topQuery = " and IsHomePage=0";
            }
            else if (top.Equals("2"))
            {
                topQuery = " and IsHomePage=1";
            }
            return string.Format("{0}{1}", checkQuery, topQuery);
        }

        public virtual int GetCount(string title, string check, string top)
        {
            string queryWhere = parseQueryStr(check, top);
            SqlParameter[] parameters ={
              new SqlParameter("@TableName",SqlDbType.VarChar),
                            new SqlParameter("@docount",SqlDbType.Bit),
                            new SqlParameter("@Title",SqlDbType.NVarChar),
                            new SqlParameter("@QueryWhere",SqlDbType.NVarChar)
            };
            parameters[0].Value = TableName;
            parameters[1].Value = true;
            parameters[2].Value = title;
            parameters[3].Value = queryWhere;

            string storeStri = "YC_Module_Paging_Proc";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, storeStri, parameters))
            {
                if (dr.Read())
                {
                    return dr.GetInt32(0);
                }
            }
            return 0;
        }

        private List<SqlParameter> GetParameterList(T model, bool isUpdate)
        {
            List<SqlParameter> parameters = new List<SqlParameter>() {							
							new SqlParameter("@CategoryID",SqlDbType.Int),
							new SqlParameter("@Title",SqlDbType.NVarChar),
							new SqlParameter("@Keywords",SqlDbType.NVarChar),
							new SqlParameter("@Description",SqlDbType.NVarChar),
							new SqlParameter("@IsHomePage",SqlDbType.Bit),
							new SqlParameter("@Access",SqlDbType.Int),
							new SqlParameter("@Hits",SqlDbType.Int),
							new SqlParameter("@IsPopular",SqlDbType.Bit),
							new SqlParameter("@DiggTop",SqlDbType.Int),
							new SqlParameter("@DiggStep",SqlDbType.Int),
							new SqlParameter("@Active",SqlDbType.Bit),
							new SqlParameter("@Lang",SqlDbType.VarChar),
							new SqlParameter("@Content",SqlDbType.NText),
                            new SqlParameter("@Author",SqlDbType.NVarChar),
                            new SqlParameter("@Source",SqlDbType.NVarChar)
			};
            parameters[0].Value = model.CategoryID;
            parameters[1].Value = model.Title;
            if (model.Keywords != String.Empty)
                parameters[2].Value = model.Keywords;
            else
                parameters[2].Value = DBNull.Value;

            if (model.Description != String.Empty)
                parameters[3].Value = model.Description;
            else
                parameters[3].Value = DBNull.Value;
            parameters[4].Value = model.IsHomePage;
            if (model.Access != null)
                parameters[5].Value = model.Access;
            else
                parameters[5].Value = DBNull.Value;
            parameters[6].Value = model.Hits;
            parameters[7].Value = model.IsPopular;
            if (model.DiggTop != null)
                parameters[8].Value = model.DiggTop;
            else
                parameters[8].Value = DBNull.Value;
            if (model.DiggStep != null)
                parameters[9].Value = model.DiggStep;
            else
                parameters[9].Value = DBNull.Value;
            parameters[10].Value = model.Active;
            if (model.Lang != String.Empty)
                parameters[11].Value = model.Lang;
            else
                parameters[11].Value = DBNull.Value;
            if (model.Content != String.Empty)
                parameters[12].Value = model.Content;
            else
                parameters[12].Value = DBNull.Value;
            if (model.Author != String.Empty)
                parameters[13].Value = model.Author;
            else
                parameters[13].Value = DBNull.Value;
            if (model.Source != String.Empty)
                parameters[14].Value = model.Source;
            else
                parameters[14].Value = DBNull.Value;

            if (isUpdate)
            {
                SqlParameter param = new SqlParameter("@ID", SqlDbType.Int);
                param.Value = model.ID;
                parameters.Add(param);
            }

            return parameters;
        }

        /// <summary>
        /// consider if use cache dependancy
        /// </summary>
        public virtual IList<T> GetList(int top, string fields, string strwhere ,string order)
        {
            IList<T> Obj = new List<T>();
            string _top = string.Empty;
            if (top != 0)
            {
                _top = "top " + top.ToString();
            }
            string qStr = string.Format("select {4} {0} from {1} where 1=1 {2} {3}", fields, TableName, strwhere, order, _top); ;
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, qStr))
            {
                while (dr.Read())
                {
                    Obj.Add(PopulatePartialModelFromDr(dr));
                }
            }
            return Obj;
        }

        /// <summary>
        /// Get YCModuleArticals List
        /// </summary>
        /// <returns>List</returns>
        public virtual IList<T> GetList(string title, string check, string top, int pageSize, int pageCurrent)
        {
            string queryWhere = parseQueryStr(check, top);
            IList<T> Obj = new List<T>();
            SqlParameter[] parameters ={

                            new SqlParameter("@TableName",SqlDbType.VarChar),
                            new SqlParameter("@PageSize",SqlDbType.Int),
                            new SqlParameter("@PageCurrent",SqlDbType.Int),
                            new SqlParameter("@FdShow",SqlDbType.NVarChar),
                            new SqlParameter("@FdOrder",SqlDbType.NVarChar),
                            new SqlParameter("@Title",SqlDbType.NVarChar),
                            new SqlParameter("@QueryWhere",SqlDbType.NVarChar)
            };
            parameters[0].Value = TableName;
            parameters[1].Value = pageSize;
            parameters[2].Value = pageCurrent;
            parameters[3].Value = "*";
            parameters[4].Value = "UpdateTime desc";
            parameters[5].Value = title;
            parameters[6].Value = queryWhere;

            string storeStri = "YC_Module_Paging_Proc";
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.StoredProcedure, storeStri, parameters))
            {
                while (dr.Read())
                {
                    Obj.Add(PopulateModelFromDr(dr));
                }
            }
            return Obj;
        }


        public int UpdateOneField(int id, string fieldName, object value)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append(string.Format("update {0} set ",TableName));
            sqlStr.Append(fieldName + "=@fieldValue");
            sqlStr.Append(" where ID=@ID");
            SqlParameter[] parameters ={
							new SqlParameter("@ID",SqlDbType.Int),
			                new SqlParameter("@fieldValue",value)
			};
            parameters[0].Value = id;
            return SqlHelper.ExecuteNonQuery(CommandType.Text, sqlStr.ToString(), parameters);
        }

        /// <summary>
        /// Get Model List
        /// </summary>
        /// <returns>List</returns>
        public IList<T> GetList(string sqlStr)
        {
            IList<T> Obj = new List<T>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr))
            {
                while (dr.Read())
                {
                    Obj.Add(PopulateModelFromDr(dr));
                }
            }
            return Obj;
        }

        /// <summary>
        /// get a YCModule object 
        /// </summary>
        /// <param name=id>ID</param>
        /// <returns>YCModuleArtical object</returns>
        public T GetModel(int id)
        {
            T _obj = new T();
            SqlParameter[] parameters ={
			new SqlParameter("@ID",SqlDbType.Int)
			};
            parameters[0].Value = id;
            string sqlStr = string.Format("select * from {0} where ID=@ID", TableName);
            using (SqlDataReader dr = SqlHelper.ExecuteReader(CommandType.Text, sqlStr, parameters))
            {
                while (dr.Read())
                {
                    _obj = PopulateModelFromDr(dr);
                }
            }
            return _obj;
        }     
    }
}
